import datetime
from typing import List

from sqlalchemy import or_, and_, Integer, func
from sqlalchemy.orm import Session

from module_admin.dao.cms_order_dispatch_dao import CmsOrderDispatchDao
from module_admin.entity.do.cms_car import CmsCar
from module_admin.entity.do.cms_driver import CmsDriver
from module_admin.entity.do.cms_order import CmsOrder
from module_admin.entity.do.cms_order_bill import CmsOrderBill
from module_admin.entity.do.cms_order_execute import CmsOrderExecute
from module_admin.entity.vo.cms_order_bill_vo import OrderBillModel, OrderBillModelForQuery, OrderBillModelForPage, \
    OrderBillModelForUpdate, OrderBillPageObject
from module_admin.entity.vo.cms_order_vo import CrudOrderResponse
from utils.time_format_util import object_format_datetime, list_format_datetime


class CmsOrderBillDao:
    """
    部门管理模块数据库操作层
    """

    @classmethod
    def create_order_bill(cls, db: Session, page_object: OrderBillModel):
        cms_order = CmsOrderBill(**page_object.dict())
        db.add(cms_order)
        db.flush()
        db.commit()

    @classmethod
    def update_order_bill_by_id(cls, db: Session, order_update_dict: dict):
        db.query(CmsOrderBill) \
            .filter(CmsOrderBill.id == order_update_dict.get('id')) \
            .update(order_update_dict)
        db.commit()

    @classmethod
    def update_order_bill_by_order_id(cls, db: Session, order_update: OrderBillModelForUpdate, order_id: str):
        order_bill: CmsOrderBill = CmsOrderBillDao.get_one_by_order_id(db, order_id)
        if order_update.sheet_mileage is not None:
            order_bill.sheet_mileage = order_update.sheet_mileage
        if order_update.system_mileage is not None:
            order_bill.system_mileage = order_update.system_mileage
        db.commit()

    @classmethod
    def get_one_by_id(cls, db: Session, id: int):
        first = db.query(CmsOrderBill).filter(CmsOrderBill.id == id).first()
        return object_format_datetime(first)

    @classmethod
    def get_one_by_order_id(cls, db: Session, order_id: str):
        first = db.query(CmsOrderBill).filter(CmsOrderBill.order_id == order_id).first()
        return object_format_datetime(first)

    @classmethod
    def list(cls, db: Session, query_object: OrderBillModelForQuery):
        # 时间
        if query_object.car_start_time:
            query_object.car_start_time = datetime.datetime.combine(query_object.car_start_time, datetime.time(0, 0, 0))
        if query_object.car_end_time:
            query_object.car_end_time = datetime.datetime.combine(query_object.car_end_time, datetime.time(23, 59, 59))
        query_for_bill = db.query(CmsOrderBill.id.label("id"),
                                  CmsOrderBill.order_id.label("order_id"),
                                  CmsOrder.id.label("id_details"),
                                  CmsOrder.department_id.label("department_name"),
                                  CmsCar.car_number.label("car_number"),
                                  CmsDriver.driver_name.label("driver_name"),
                                  CmsOrder.use_car_type.label("use_car_type"),
                                  CmsOrder.dispatch_type.label("dispatch_type"),
                                  CmsOrderBill.system_mileage.label("system_mileage"),
                                  CmsOrderBill.sheet_mileage.label("sheet_mileage"),
                                  CmsOrderBill.system_mileage.label("mileage")
                                  if CmsOrderExecute.bill_type == "beidou" else CmsOrderBill.sheet_mileage.label(
                                      "mileage"),
                                  CmsOrderBill.expenses.label("expenses"),
                                  CmsOrderExecute.reality_begin_time.label("reality_begin_time"),
                                  CmsOrderExecute.reality_end_time.label("reality_end_time"),
                                  CmsOrderBill.state.label("state")) \
            .outerjoin(CmsOrder, CmsOrderBill.order_id == CmsOrder.order_id) \
            .outerjoin(CmsOrderExecute, CmsOrderBill.order_id == CmsOrderExecute.order_id) \
            .outerjoin(CmsCar, CmsOrderBill.car_id == CmsCar.car_id)\
            .outerjoin(CmsDriver, CmsOrderBill.driver_id == CmsDriver.driver_id)
        filters = []
        if query_object.order_id and query_object.order_id.strip():
            filters.append(CmsOrder.order_id == query_object.order_id)
        if query_object.department_id and query_object.department_id.strip():
            filters.append(CmsOrder.department_id == query_object.department_id)
        if query_object.department_name and query_object.department_name.strip():
            filters.append(CmsOrder.department_id.like(f'%{query_object.department_name}%'))
        if query_object.order_create_by and query_object.order_create_by.strip():
            filters.append(CmsOrder.create_by.like(f'%{query_object.order_create_by}%'))
        if query_object.state and query_object.state.strip():
            filters.append(CmsOrderBill.state == query_object.state)
        if query_object.car_number and query_object.car_number.strip():
            filters.append(CmsCar.car_number == query_object.car_number)
        if query_object.car_start_time:
            filters.append(
                CmsOrderExecute.reality_begin_time >= query_object.car_start_time.strftime('%Y-%m-%d %H:%M:%S'))
        if query_object.car_start_time:
            filters.append(
                CmsOrderExecute.reality_begin_time <= query_object.car_end_time.strftime('%Y-%m-%d %H:%M:%S'))
        if filters:
            query_for_bill = query_for_bill.filter(*filters)
        post_list_bill: List[OrderBillModelForPage] = list_format_datetime(
            query_for_bill.order_by(CmsOrderBill.create_time.desc()).all())
        post_list_bill_page = []
        for e in post_list_bill:
            page = OrderBillModelForPage()
            page.id = e.id
            page.order_id = e.order_id
            page.id_details = e.id_details
            page.department_name = e.department_name
            page.car_number = e.car_number
            page.driver_name = e.driver_name
            page.use_car_type = e.use_car_type
            page.dispatch_type = e.dispatch_type
            page.system_mileage = e.system_mileage
            page.sheet_mileage = e.sheet_mileage
            page.mileage = e.mileage
            page.expenses = e.expenses
            page.reality_begin_time = e.reality_begin_time
            page.reality_end_time = e.reality_end_time
            page.time = (e.reality_end_time - e.reality_begin_time).seconds / 60 \
                if e.reality_end_time is not None and e.reality_begin_time is not None else None
            page.state = e.state
            post_list_bill_page.append(page)
        return post_list_bill_page

    @classmethod
    def page(cls, db, query_object: OrderBillPageObject):
        page_num = query_object.page_num
        page_size = query_object.page_size
        skip = (page_num - 1) * page_size  # 计算跳过的记录数
        limit = page_size  # 每页返回的记录数
        # 时间
        if query_object.car_start_time:
            query_object.car_start_time = datetime.datetime.combine(query_object.car_start_time, datetime.time(0, 0, 0))
        if query_object.car_end_time:
            query_object.car_end_time = datetime.datetime.combine(query_object.car_end_time, datetime.time(23, 59, 59))
        query_for_bill = db.query(CmsOrderBill.id.label("id"),
                                  CmsOrderBill.order_id.label("order_id"),
                                  CmsOrder.id.label("id_details"),
                                  CmsOrder.department_id.label("department_name"),
                                  CmsCar.car_number.label("car_number"),
                                  CmsDriver.driver_name.label("driver_name"),
                                  CmsOrder.use_car_type.label("use_car_type"),
                                  CmsOrder.dispatch_type.label("dispatch_type"),
                                  CmsOrderBill.system_mileage.label("system_mileage"),
                                  CmsOrderBill.sheet_mileage.label("sheet_mileage"),
                                  CmsOrderBill.system_mileage.label("mileage")
                                  if CmsOrderExecute.bill_type == "beidou" else CmsOrderBill.sheet_mileage.label(
                                      "mileage"),
                                  CmsOrderBill.expenses.label("expenses"),
                                  CmsOrderExecute.reality_begin_time.label("reality_begin_time"),
                                  CmsOrderExecute.reality_end_time.label("reality_end_time"),
                                  CmsOrderBill.state.label("state")) \
            .outerjoin(CmsOrder, CmsOrderBill.order_id == CmsOrder.order_id) \
            .outerjoin(CmsOrderExecute, CmsOrderBill.order_id == CmsOrderExecute.order_id) \
            .outerjoin(CmsCar, CmsOrderBill.car_id == CmsCar.car_id) \
            .outerjoin(CmsDriver, CmsOrderBill.driver_id == CmsDriver.driver_id)
        filters = []
        if query_object.order_id and query_object.order_id.strip():
            filters.append(CmsOrder.order_id == query_object.order_id)
        if query_object.department_id and query_object.department_id.strip():
            filters.append(CmsOrder.department_id == query_object.department_id)
        if query_object.department_name and query_object.department_name.strip():
            filters.append(CmsOrder.department_id.like(f'%{query_object.department_name}%'))
        if query_object.order_create_by and query_object.order_create_by.strip():
            filters.append(CmsOrder.create_by.like(f'%{query_object.order_create_by}%'))
        if query_object.state and query_object.state.strip():
            filters.append(CmsOrderBill.state == query_object.state)
        if query_object.car_number and query_object.car_number.strip():
            filters.append(CmsCar.car_number == query_object.car_number)
        if query_object.car_start_time:
            filters.append(
                CmsOrderExecute.reality_begin_time >= query_object.car_start_time.strftime('%Y-%m-%d %H:%M:%S'))
        if query_object.car_start_time:
            filters.append(
                CmsOrderExecute.reality_begin_time <= query_object.car_end_time.strftime('%Y-%m-%d %H:%M:%S'))
        if filters:
            query_for_bill = query_for_bill.filter(*filters)
        post_list_bill: List[OrderBillModelForPage] = list_format_datetime(
            query_for_bill.order_by(CmsOrderBill.create_time.desc()).offset(skip).limit(limit).all())
        post_list_bill_page = []
        for e in post_list_bill:
            page = OrderBillModelForPage()
            page.id = e.id
            page.order_id = e.order_id
            page.id_details = e.id_details
            page.department_name = e.department_name
            page.car_number = e.car_number
            page.driver_name = e.driver_name
            page.use_car_type = e.use_car_type
            page.dispatch_type = e.dispatch_type
            page.system_mileage = e.system_mileage
            page.sheet_mileage = e.sheet_mileage
            page.mileage = e.mileage
            page.expenses = e.expenses
            page.reality_begin_time = e.reality_begin_time
            page.reality_end_time = e.reality_end_time
            page.time = (e.reality_end_time - e.reality_begin_time).seconds / 60 \
                if e.reality_end_time is not None and e.reality_begin_time is not None else None
            page.state = e.state
            post_list_bill_page.append(page)
        return post_list_bill_page

    @classmethod
    def count(cls, db, query_object: OrderBillPageObject):
        # 时间
        if query_object.car_start_time:
            query_object.car_start_time = datetime.datetime.combine(query_object.car_start_time, datetime.time(0, 0, 0))
        if query_object.car_end_time:
            query_object.car_end_time = datetime.datetime.combine(query_object.car_end_time, datetime.time(23, 59, 59))
        query_for_bill = db.query(CmsOrderBill.id.label("id"),
                                  CmsOrderBill.order_id.label("order_id"),
                                  CmsOrder.id.label("id_details"),
                                  CmsOrder.department_id.label("department_name"),
                                  CmsCar.car_number.label("car_number"),
                                  CmsDriver.driver_name.label("driver_name"),
                                  CmsOrder.use_car_type.label("use_car_type"),
                                  CmsOrder.dispatch_type.label("dispatch_type"),
                                  CmsOrderBill.system_mileage.label("system_mileage"),
                                  CmsOrderBill.sheet_mileage.label("sheet_mileage"),
                                  CmsOrderBill.system_mileage.label("mileage")
                                  if CmsOrderExecute.bill_type == "beidou" else CmsOrderBill.sheet_mileage.label(
                                      "mileage"),
                                  CmsOrderBill.expenses.label("expenses"),
                                  CmsOrderExecute.reality_begin_time.label("reality_begin_time"),
                                  CmsOrderExecute.reality_end_time.label("reality_end_time"),
                                  CmsOrderBill.state.label("state")) \
            .outerjoin(CmsOrder, CmsOrderBill.order_id == CmsOrder.order_id) \
            .outerjoin(CmsOrderExecute, CmsOrderBill.order_id == CmsOrderExecute.order_id) \
            .outerjoin(CmsCar, CmsOrderBill.car_id == CmsCar.car_id) \
            .outerjoin(CmsDriver, CmsOrderBill.driver_id == CmsDriver.driver_id)
        filters = []
        if query_object.order_id and query_object.order_id.strip():
            filters.append(CmsOrder.order_id == query_object.order_id)
        if query_object.department_id and query_object.department_id.strip():
            filters.append(CmsOrder.department_id == query_object.department_id)
        if query_object.department_name and query_object.department_name.strip():
            filters.append(CmsOrder.department_id.like(f'%{query_object.department_name}%'))
        if query_object.order_create_by and query_object.order_create_by.strip():
            filters.append(CmsOrder.create_by.like(f'%{query_object.order_create_by}%'))
        if query_object.state and query_object.state.strip():
            filters.append(CmsOrderBill.state == query_object.state)
        if query_object.car_number and query_object.car_number.strip():
            filters.append(CmsCar.car_number == query_object.car_number)
        if query_object.car_start_time:
            filters.append(
                CmsOrderExecute.reality_begin_time >= query_object.car_start_time.strftime('%Y-%m-%d %H:%M:%S'))
        if query_object.car_start_time:
            filters.append(
                CmsOrderExecute.reality_begin_time <= query_object.car_end_time.strftime('%Y-%m-%d %H:%M:%S'))
        if filters:
            query_for_bill = query_for_bill.filter(*filters)
        return query_for_bill.order_by(CmsOrderBill.create_time.desc()).count()